﻿using System.Data.SQLite;
using DVDTek_Data_Object.Collection;
using DVDTek_Data_Object.DataObject;

namespace DVDTek_Data_Object.Accessor
{
    internal class BorrowerAccessor
    {
        #region Constants
        internal const string TABLE_NAME = "Borrower";
        internal const string FN_PRIMARY_KEY = "id";
        private const string FN_ID = "id";
        private const string FN_FIRST_NAME = "first_name";
        private const string FN_LAST_NAME = "last_name";
        #endregion

        #region Members
        private static BorrowerAccessor borrowerAccessor;
        internal static BorrowerAccessor Instance
        {
            get
            {
                if (borrowerAccessor == null)
                    borrowerAccessor = new BorrowerAccessor();

                return borrowerAccessor;
            }
        }

        internal string CreateTableQuery
        {
            get
            {
                return string.Concat(
                    "CREATE TABLE ", TABLE_NAME, " (",
                    FN_ID, " integer PRIMARY KEY AUTOINCREMENT NOT NULL, ",
                    FN_FIRST_NAME, " varchar(80) NOT NULL, ",
                    FN_LAST_NAME, " varchar(80) NOT NULL, ",
                    "CONSTRAINT un_", FN_FIRST_NAME, "_", FN_LAST_NAME,
                    " UNIQUE(", FN_FIRST_NAME, ",", FN_LAST_NAME, "));");
            }
        }
        #endregion

        #region Constructors
        private BorrowerAccessor()
        {
        }
        #endregion

        #region Insert / Update / Delete
        internal int Insert(string first_name, string last_name)
        {
            string query = string.Concat(
                "INSERT INTO ", TABLE_NAME,
                " (", FN_ID, ",", FN_FIRST_NAME, ",", FN_LAST_NAME, ")",
                " VALUES ",
                "('', '", Common.QuoteEscape(first_name), "', '", Common.QuoteEscape(last_name), "');",
                "SELECT last_insert_rowid();");

            return (int)Common.ExecuteScalarQuery(query);
        }

        internal void Update(int id, string first_name, string last_name)
        {
            string query = string.Concat(
                "UPDATE ", TABLE_NAME, " SET ",
                FN_FIRST_NAME, "='", Common.QuoteEscape(first_name), "', ",
                FN_LAST_NAME, "='", Common.QuoteEscape(last_name), "'",
                " WHERE ", FN_ID, "='", id, "';");

            Common.ExecuteNonQuery(query);
        }

        internal void Delete(int id)
        {
            string query = string.Concat(
                "DELETE FROM ", TABLE_NAME,
                " WHERE ", FN_ID, "='", id, "';");

            Common.ExecuteNonQuery(query);
        }

        internal void Delete(string first_name, string last_name)
        {
            string query = string.Concat(
                "DELETE FROM ", TABLE_NAME,
                " WHERE ", FN_FIRST_NAME, "='", Common.QuoteEscape(first_name), "'",
                " AND ", FN_LAST_NAME, "='", Common.QuoteEscape(last_name), "';");

            Common.ExecuteNonQuery(query);
        }
        #endregion

        #region Select
        internal BorrowerDataObject Select(int id)
        {
            string query = string.Concat(
                "SELECT * FROM ", TABLE_NAME,
                " WHERE ", FN_ID, "='", id, "';");

            SQLiteDataReader reader = Common.ExecuteQuery(query);

            if (reader.Read())
                return new BorrowerDataObject(reader.GetInt32(0), reader.GetString(1), reader.GetString(2));
            else
                return null;
        }
        internal BorrowerDataObject Select(string first_name, string last_name)
        {
            string query = string.Concat(
                "SELECT * FROM ", TABLE_NAME,
                " WHERE ", FN_FIRST_NAME, "='", Common.QuoteEscape(first_name), "'",
                " AND ", FN_LAST_NAME, "='", Common.QuoteEscape(last_name), "';");

            SQLiteDataReader reader = Common.ExecuteQuery(query);

            if (reader.Read())
                return new BorrowerDataObject(reader.GetInt32(0), reader.GetString(1), reader.GetString(2));
            else
                return null;
        }

        internal BorrowerCollection SelectAll()
        {
            string query = string.Concat(
                "SELECT * FROM ", TABLE_NAME, ";");

            SQLiteDataReader reader = Common.ExecuteQuery(query);
            BorrowerCollection borrowers = new BorrowerCollection();

            while (reader.Read())
                borrowers.Add(new BorrowerDataObject(reader.GetInt32(0), reader.GetString(1), reader.GetString(2)));

            return borrowers;
        }
        #endregion

        internal bool Exist(string first_name, string last_name)
        {
            return Select(first_name, last_name) != null;
        }
    }
}
